#!/usr/bin/perl -w

use strict;
use warnings;
use Data::Dumper;
use XML::LibXML;
use Getopt::Long;
use DBI;

my $schemaPath = "../schema/";
my @add;
my @verify;

## mysql user database name
my $db ="mysql";
## mysql database user name
my $user = "DrDocRoot";
## mysql database password
my $pass = "DrDoc5m4ll";
## user hostname : This should be "localhost" but it can be diffrent too
my $host="drdoc2.cfcbem1draxy.us-east-1.rds.amazonaws.com:3306";

GetOptions ('add=s' => \@add, 'verify=s' => \@verify, 'spath=s' => \$schemaPath, 'db=s' => \$db, 'user=s' => \$user, 'password=s' => \$pass, 'hostname=s' => \$host);

#if ($add && $verify) {
#	die ("Cannot both add and veify");
#}

foreach my $doc (@verify) {
	my $pdoc = XML::LibXML->new->parse_file($doc);
	my $xsd = $schemaPath.$pdoc->documentElement()->nodeName.".xsd";
	if (verify($pdoc, $xsd)) {
		print ("$doc is valid for schema $xsd\n\n");
	}
	else {
		print ("$doc invalid for schema $xsd: $@i\n\n");	
	}
}

sub verify {
	my $schema = XML::LibXML::Schema->new(location=>$_[1]);	
	eval { $schema->validate( $_[0] ) };
	if ($@) {
		return 0;
	}
	else {
		return 1;
	}
}

if (@add){
	my $dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass)
		or die "Cannot connect to $host\n";
}

foreach my $doc (@add) {
	my $pdoc = XML::LibXML->new->parse_file($doc);
	my $xsd = $schemaPath.$pdoc->documentElement()->nodeName.".xsd";
#	if (table $schemaPath.$pdoc->documentElement()->nodeName does not exist) {
#		my $query = "create table if not exists ".$pdoc->documentElement()->nodeName." ("
#	}
	if (verify($pdoc, $xsd)) {
		my $query = "insert into ".$pdoc->documentElement()->nodeName;	
		my @values;
		foreach my $attr ($pdoc->documentElement()->nonBlankChildNodes()) {
			print ($attr->nodeName."\n");
			print ($attr->textContent."\n\n");
			$query = $query." ".$attr->nodeName;
			push @values,$attr->textContent;
		}
		$query = $query." values(";
		foreach my $val (@values)
		{
			$query = $query."'".$val;
			if ($val eq $values[-1]) {
				$query = $query."');";
			}
			else {$query = $query."',";}
		}
		print $query."\n";
#		my $sqlQuery  = $dbh->prepare("$attr->nodeName $attr->textContent")
#			or die "Can't prepare $query: $dbh->errstr\n";
#		my $rv = $sqlQuery->execute
#			or die "can't execute the query: $sqlQuery->errstr";
	}
	else {
		print ("Unable to verify $doc against schema $xsd: $@\n");
	}
}
